import logging
from typing import Dict, List, Tuple, Optional, Union

import mysql.connector
from mysql.connector import Error


class MySQLTool:
    """MySQL数据库操作工具类（基于官方 mysql-connector-python）"""

    def __init__(self, host='172.27.32.59', user='mes_dev', password='dE2MmyKRiM65aBMF', database='mes_dev',
                 port='3306') -> None:
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = int(port) if isinstance(port, str) else port
        self.connection: Optional[mysql.connector.MySQLConnection] = None
        self.logger = logging.getLogger(__name__)

    def _new_connection(self) -> Optional[mysql.connector.MySQLConnection]:
        try:
            conn = mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                port=self.port,
                connection_timeout=3,
            )
            conn.autocommit = True
            return conn
        except Error as e:
            self.logger.error(f"创建MySQL连接失败: {e}")
            return None

    def connect(self) -> bool:
        """建立持久连接（可选）"""
        self.connection = self._new_connection()
        if self.connection and self.connection.is_connected():
            self.logger.info(f"已连接到MySQL数据库: {self.database}")
            return True
        return False

    def disconnect(self) -> None:
        if self.connection:
            try:
                self.connection.close()
                self.logger.info("MySQL数据库连接已关闭")
            except Exception:
                pass
            finally:
                self.connection = None

    def _get_active_connection(self) -> Tuple[Optional[mysql.connector.MySQLConnection], bool]:
        """返回一个可用连接以及是否为临时创建（需在用后关闭）"""
        if self.connection and self.connection.is_connected():
            return self.connection, False
        # 临时短连接
        conn = self._new_connection()
        return conn, True

    def execute_query(self, sql: str, params: Optional[Union[Tuple, Dict, List]] = None) -> List[Tuple]:
        conn, temp = self._get_active_connection()
        if not conn:
            return []
        cursor = None
        try:
            cursor = conn.cursor()
            cursor.execute(sql, params)
            rows = cursor.fetchall()
            return rows or []
        except Error as e:
            self.logger.error(f"执行查询SQL失败: {e}")
            return []
        finally:
            try:
                if cursor:
                    cursor.close()
            finally:
                if temp and conn:
                    conn.close()

    def execute_insert(self, sql: str, params: Optional[Union[Tuple, Dict, List]] = None) -> int:
        conn, temp = self._get_active_connection()
        if not conn:
            return -1
        cursor = None
        try:
            cursor = conn.cursor()
            cursor.execute(sql, params)
            return cursor.rowcount or 0
        except Error as e:
            self.logger.error(f"执行插入SQL失败: {e}")
            return -1
        finally:
            try:
                if cursor:
                    cursor.close()
            finally:
                if temp and conn:
                    conn.close()

    def execute_many_inserts(self, sql: str, params_list: List[Union[Tuple, Dict]]) -> int:
        conn, temp = self._get_active_connection()
        if not conn:
            return -1
        cursor = None
        try:
            cursor = conn.cursor()
            cursor.executemany(sql, params_list)
            return cursor.rowcount or 0
        except Error as e:
            self.logger.error(f"执行批量插入SQL失败: {e}")
            return -1
        finally:
            try:
                if cursor:
                    cursor.close()
            finally:
                if temp and conn:
                    conn.close()
